﻿"""
判定加完系数的测试数据与标准件是否相符，相符修改CF2 CF3并下发湿胶或者成品坐标点
"""
import os
import shutil
import pyodbc
from datetime import datetime
import pandas as pd
from sqlalchemy import create_engine
from sortingdataprocess import get_standpart_data, get_test_data, cal_coef, cal_end_coef, get_cf, get_item_code, \
    decrease_data
from getcobspec import *
from setiniconfig import *


def config_mp(form3_infos, machine_name, machine_label, pre_path, cnxn, conn, engine, logger, CCT, CCT_String):
    """

    :param form3_infos: 步骤三的字典数据：工单号、批次号1、2、标准件型号、机台号、工号、临时文件路径、上传的文件名称、二步文件生成路径、二步测试数据保存路径、校准系数
    :param pre_path: 第二步校准数据保存路径
    :param cnxn: 权限高的数据库连接（用于更新、修改等）
    :param conn: 权限低的数据库连接（用于查询）
    :param engine: 权限高的数据库engine
    :param logger: 整个flask的日志记录器
    :param CCT: 双色温区分标志：low/high;低色温/正常 均使用low,高色温 使用high
    :param CCT_String:色温字符串，用于查询双色温对应标准件数据
    :return:
    """
    # 定义返回值，排除未定以返回值时程序报错
    flash_result = {}
    endconf_path = None
    lotID3 = None
    mpstate = False
    # 创建游标
    cursor = cnxn.cursor()
    cursor.fast_executemany = True
    # 创建游标
    cursor1 = conn.cursor()
    cursor1.fast_executemany = True
    pd.set_option('display.max_columns', None)
    # 获取品号
    item_code = get_item_code(form3_infos['MoID'])

    if not CCT:
        df_color = get_color_stand(item_code)
        df_color_2 = get_color_stand_5(item_code)
        CF2_pars = get_cf(item_code, 2)
        CF3_pars = get_cf(item_code, 3)
        standpart_data = get_standpart_data(item_code)
        df_bin = get_bin_range(item_code)
        df_extra = get_extra_bin(item_code)
        df_range = get_spec_range(item_code)
    else:
        df_color = get_color_stand(item_code, CCT)
        df_color_2 = get_color_stand_5(item_code, CCT)
        CF2_pars = get_cf(item_code, 2, CCT)
        CF3_pars = get_cf(item_code, 3, CCT)
        standpart_data = get_standpart_data(item_code, CCT_String)
        df_bin = get_bin_range(item_code, CCT)
        df_extra = get_extra_bin(item_code, CCT)
        df_range = get_spec_range(item_code, CCT)

    # 读取测试数据
    test_data, error = get_test_data(form3_infos['temp_path'], machine_name, logger)
    # 情况1：测试数据为空或读取错误
    if test_data.empty:
        logger.warn(f"{form3_infos['MachineID']}号设备，{form3_infos['MoID']}工单--上传为空的测试数据")
        flash_result[1] = f"{machine_label}<br>" \
                          f"读取上传数据发生错误或者上传数据为空,截图反馈至实验室<br>问题描述:{error}"
        return flash_result, endconf_path, lotID3

    # 去除电流为0以及光通量为0的数据
    test_data = test_data[test_data['Current_mA'] != 0]
    test_data.dropna(subset=['Current_mA'], inplace=True)
    test_data = test_data[test_data['LuminousFlux_lm'] != 0]
    test_data.dropna(subset=['LuminousFlux_lm'], inplace=True)
    # 情况2：'5101-'开头工单，使用少于3条数据校准
    if form3_infos['MoID'].startswith('5101-') and len(test_data) < 3:
        logger.warn(f"{form3_infos['MachineID']}号设备，{form3_infos['MoID']}工单--测试数据少于3条!")
        flash_result[2] = f"{machine_label}<br>" \
                          f"请保证3个或3个以上标准灯正常点亮,有效数据条数:{len(test_data)}"
        return flash_result, endconf_path, lotID3

    # 校准系数计算
    compare_data = cal_coef(standpart_data, test_data)
    # 情况3：校准系数为空,编号不对应
    if compare_data.empty:
        list1 = sorted(list(standpart_data['StandardPartNO']))
        logger.warn(f"{form3_infos['MachineID']}号设备，{form3_infos['MoID']}工单--测试数据与标准件数据编号不对应!")
        flash_result[3] = f"{machine_label}<br>" \
                          f"测试数据与标准件数据编号不对应,标准件编号:{list1}"
        return flash_result, endconf_path, lotID3

    compare_data.drop(index='校准系数', inplace=True)
    compare_data['dlm'] = compare_data['dlm'].apply(lambda x: x - 1)
    total_counts = len(compare_data)
    # 每个灯进行判定，不合格数据去除
    # 去除列表
    remove_list = []
    for index in range(total_counts):
        # 判定标准
        if abs(compare_data.iloc[index]['dx']) > 0.0015 or abs(compare_data.iloc[index]['dy']) > 0.0015 \
                or abs(compare_data.iloc[index]['dRa']) > 2 or abs(compare_data.iloc[index]['dR9']) > 2 \
                or abs(compare_data.iloc[index]['dlm']) > 0.03:
            remove_list.append(index)
    remove_list = list(set(remove_list))

    cal_requirments = {1: 1, 2: 1, 3: 2, 4: 2, 5: 3, 6: 3, 7: 4, 8: 5, 9: 6, 10: 6}
    if total_counts not in cal_requirments.keys():
        flash_result[20] = f"{machine_label}<br>" \
                           f"测试数据条数小于1或者大于10"
        logger.warn(f"{form3_infos['MachineID']}号设备,测试数据条数小于1或者大于10")
        return flash_result, endconf_path, lotID3
    # 校准失败数量超标记得使用to_html转为表格
    elif (total_counts in cal_requirments) and (
            (total_counts - len(remove_list)) < cal_requirments[total_counts]):
        flash_result['校准异常'] = compare_data.iloc[remove_list]
        return flash_result, endconf_path, lotID3

    # 生成删除数据的列表
    NO_list = []
    for i in remove_list:
        NO_list.append(int(compare_data.iloc[i]['StandardPartNO']))
    # 删除不合格，并重新计算校准系数
    # 获取服务器存储的校准数据，进行重新计算
    # print(f"文件路径：{pre_path}")
    test_data2,error = get_test_data(pre_path, machine_name, logger)
    test_data2 = test_data2[test_data2['Current_mA'] != 0]
    test_data2.dropna(subset=['Current_mA'], inplace=True)
    test_data2 = test_data2[test_data2['LuminousFlux_lm'] != 0]
    test_data2.dropna(subset=['LuminousFlux_lm'], inplace=True)

    for i in remove_list:
        if i not in list(test_data2.index):
            remove_list.remove(i)
    test_data2 = test_data2.drop(index=remove_list)
    # 新的对比DataFrame
    new_compare_data = cal_coef(standpart_data, test_data2)
    # 重新赋值，替换原有的值进行最终系数的计算
    form3_infos['coef_data'] = new_compare_data.loc['校准系数'].to_dict()

    # 计算并保存差值
    save_data = decrease_data(standpart_data, test_data, form3_infos['MoID'])
    #save_data.to_sql('Compare_Data', con=engine, if_exists='append', index=None)

    # 创建上传与下载目录
    uploadsDir = os.path.join('uploads', 'caldata', str(datetime.now().year), str(datetime.now().month))
    if not os.path.exists(uploadsDir):
        os.makedirs(uploadsDir)
    downloadsDir = os.path.join('downloads', 'endcoef', str(datetime.now().year), str(datetime.now().month))
    if not os.path.exists(downloadsDir):
        os.makedirs(downloadsDir)

    # 计算最终系数
    end_coef = cal_end_coef(form3_infos['coef_data'], CF2_pars, CF3_pars)
    # 读取之前一步的校准文件
    ini_file_read(form3_infos['CoePath'])
    form3_infos['ProcessID'] = '3'
    # 获取点粉电流，电压再上限的基础上再加5V
    # todo:此处修改 若电压超过100V再加5V
    if df_range[df_range['Parameter'] == 'ForwardVoltage_V']['USL'].to_list()[0] > 50:
        voltage = (df_range[df_range['Parameter'] == 'ForwardVoltage_V']['USL'].to_list()[0]) + 8
    else:
        voltage = (df_range[df_range['Parameter'] == 'ForwardVoltage_V']['USL'].to_list()[0]) + 5
    # print(get_item_features(item_code, '电流Typ(mA)'))
    if CCT == 'low' or not CCT:
        current = get_item_features(item_code, '电流Typ(mA)').loc[0, 'FEATURE_VALUE']
    else:
        current = get_item_features(item_code, '高色温电流typ(mA)').loc[0, 'FEATURE_VALUE']
    # current = get_item_features(item_code, '电流Typ(mA)').loc[0, 'FEATURE_VALUE']
    # 情况4：视图没有电流值
    if not current:
        flash_result[40] = "视图ITEM_CODE_VALUE无电流Typ(mA)有效数据,找研发负责人"
        logger.warn(f"{form3_infos['MachineID']}号设备,视图ITEM_CODE_VALUE无电流Typ(mA)有效数据")
        return flash_result, endconf_path, lotID3

    if form3_infos['MachineID'] == 3:
        set_voltage_current(voltage, current)
        # 设置最终系数
        set_cal_factor_3(end_coef.loc['最终系数'].to_dict())
        # 设置色容差
        set_mac_bin_3(df_range, df_bin, df_extra)
        # 设置成品参数卡控
        set_range_3(df_range)
        # 设置中心点
        set_color_stand(df_color)
    elif form3_infos['MachineID'] == 4:
        set_voltage_current_4(voltage, current)
        # 设置最终系数
        set_cal_factor_4(end_coef.loc['最终系数'].to_dict(), df_range, df_bin)
        # 设置色容差
        set_mac_bin_4(df_range, df_bin, df_extra)
        # 设置中心点
        set_color_stand_4(df_color)
    elif form3_infos['MachineID'] == 5 or form3_infos['MachineID'] == 22:
        set_voltage_current_4(voltage, current)
        # 设置最终系数
        set_cal_factor_5(end_coef.loc['最终系数'].to_dict(), df_range, df_bin)
        # 设置色容差
        set_mac_bin_5(df_range, df_bin, df_extra)
        # 设置中心点
        set_color_stand_5(df_color_2)

    # 确认lotID3，存在+1，不存在初始化为1
    cursor.execute(
        f"SELECT MAX(LOTID3) FROM MPvalidation_Info1 WHERE MOID='{form3_infos['MoID']}' "
        f"AND LotID='{form3_infos['LotID']}'AND LotID2='{form3_infos['LotID2']}';")
    rows = cursor.fetchall()
    if rows[0][0] is None:
        lotID3 = 1
    else:
        lotID3 = rows[0][0] + 1
    # 添加LOTID2到列表
    form3_infos['LotID3'] = lotID3

    # 保存数据文件
    ext = os.path.splitext(form3_infos['temp_path'])[1]
    upnew_filename = 'caldata-' + form3_infos['MoID'] + '-' + str(form3_infos['LotID']) + '-' \
                     + str(form3_infos['LotID2']) + '-' + str(lotID3) + ext
    form3_infos['UploadPath'] = os.path.join(uploadsDir, upnew_filename)
    shutil.copy(form3_infos['temp_path'], form3_infos['UploadPath'])

    # 删除字典coef_data,temp_path及临时文件
    os.remove(form3_infos.pop('temp_path'))
    # 删除coef_data
    form3_infos.pop('coef_data')

    # 获取标准件的测试时间
    get_time = f"SELECT StandardPartNO as TestNO,CONVERT(char,TestTime,120) as StandardTime FROM " \
               f"StandardPartData WHERE " \
               f"StandardPartID='{form3_infos['StandardPartID']}' AND  DataStatus=1"
    time_df = pd.read_sql(get_time, engine)
    test_data = pd.merge(time_df, test_data, on='TestNO')

    # 测试数据整理并写入数据库
    pars = ['MoID', 'StandardPartID', 'MachineID', 'UserID', 'LotID', 'LotID2', 'LotID3', 'ProcessID']
    for i in pars:
        test_data[i] = form3_infos[i]
    # print(f"最终系数:{test_data}")
    # 数据写入数据库
    try:
        test_data.to_sql('MPvalidation_Data1', engine, if_exists='append', index=False)
    except Exception as e:
        logger.error(f"{form3_infos['MachineID']}号设备,{form3_infos['MoID']}--MPvalidation_Data1存储错误,{e}")
        flash_result[100] = f"{machine_label}<br>" \
                            f"校准数据MPvalidation_Data1存储错误,截图反馈至实验室<br>问题描述:{e}"
        return flash_result, endconf_path, lotID3
    else:
        if '最终系数' not in list(end_coef.index):
            logger.error(f"{form3_infos['MachineID']}号设备，{form3_infos['MoID']}工单不存在'最终系数'行")
            flash_result[55] = f"{machine_label}<br>" \
                               f"最终系数获取失败！"
            return flash_result, endconf_path, lotID3

        dict_coef_data = end_coef.loc['最终系数'].to_dict()
        # print(f"最终系数：{end_coef}")
        if CCT:
            if CCT == 'low':
                code = '低色温-最终系数'
            elif CCT == 'high':
                code = '高色温-最终系数'
            else:
                code = '最终系数'
        else:
            code = '最终系数'
        # 查询COF中StandardPartNO最大值的符合条件的数据
        sql_select = f"SELECT max(StandardPartNO) FROM COF WHERE MoID='{form3_infos['MoID']}' and CF='{code}' and " \
                     f"MachineID='{form3_infos['MachineID']}'"
        row = cursor1.execute(sql_select).fetchall()
        if row[0][0]:
            No = int(row[0][0]) + 1
        else:
            No = 1
        sql_insert = f"INSERT INTO COF (MoID,CF,ITEM_CODE,StandardPartNO," \
                     f"dU,dx,dy,dRa,dR9,dlm,dI,MachineID,Updatetime,LotID,DeleteStandard)" \
                     f"values ('{form3_infos['MoID']}','{code}','{item_code}','{No}'," \
                     f"{dict_coef_data['dU']},{dict_coef_data['dx']},{dict_coef_data['dy']}," \
                     f"{dict_coef_data['dRa']},{dict_coef_data['dR9']},{dict_coef_data['dlm']}," \
                     f"{dict_coef_data['dI']},'{form3_infos['MachineID']}','{datetime.now()}'," \
                     f"'{str(form3_infos['LotID']) + '-' + str(form3_infos['LotID2']) + '-' + str(form3_infos['LotID3'])}'," \
                     f"'{NO_list}')"
        try:
            cursor1.execute(sql_insert)
            conn.commit()
        except pyodbc.OperationalError:
            flash_result[100] = f'数据库连接断开!请关闭网页后,重新进行校准'
            logger.error(f"数据库连接断开!")
        except pyodbc.InterfaceError:
            flash_result[100] = f'数据库登陆失败!请关闭网页后,重新进行校准'
            logger.error(f"数据库登陆失败!")
        except Exception as e:
            flash_result[404] = f'{machine_label}<br>' \
                                f'第三步校准系数存储时发生错误,截图反馈至实验室<br>问题描述:{e}'
            logger.error(f"数据库COF表插入失败，问题描述:{e}")
        else:
            if CCT:
                str1 = '-低色温' if CCT == 'low' else '-高色温'
                down_filename = '量产' + form3_infos['ProcessID'] + '-' + form3_infos['MoID'] + '-' + \
                                str(form3_infos['LotID']) + '-' + str(
                    form3_infos['LotID2']) + '-' + str(
                    lotID3) + str1 + '.ini'
            else:
                down_filename = '量产' + form3_infos['ProcessID'] + '-' + form3_infos['MoID'] + '-' + \
                                str(form3_infos['LotID']) + '-' + str(
                    form3_infos['LotID2']) + '-' + str(
                    lotID3) + '.ini'
            endconf_path = os.path.join(downloadsDir, down_filename)
            ini_file_save(endconf_path)
            form3_infos['DownloadPath'] = endconf_path
            # 插入当前时间
            form3_infos['UpdateTime'] = str(datetime.now())
            # 遍历字典
            kvs = [(k, v) for k, v in form3_infos.items() if v is not None]
            # 创建sql语句
            sql_data = f"INSERT INTO MPvalidation_Info1({','.join(i[0] for i in kvs)}) " \
                       f"VALUES ({','.join(repr(i[1]) for i in kvs)});"
            try:
                cursor.execute(sql_data)
                cnxn.commit()
            except pyodbc.OperationalError:
                flash_result[100] = f'数据库连接断开!请关闭网页后,重新进行校准'
                logger.error(f"数据库连接断开!")
            except pyodbc.InterfaceError:
                flash_result[100] = f'数据库登陆失败!请关闭网页后,重新进行校准'
                logger.error(f"数据库登陆失败!")
            except Exception as e:
                flash_result[404] = f'{machine_label}<br>' \
                                    f'第二步最终信息存储时发生错误,截图反馈至实验室<br>问题描述:{e}'
                logger.error(f"数据库MPvalidation_Info1表插入失败，问题描述:{e}")
            else:
                flash_result['成功'] = '最终系数生成成功'
                logger.info(
                    f"{form3_infos['MachineID']}号,{form3_infos['MoID']}最终系数计算成功-{form3_infos['CoePath']}")
    return flash_result, endconf_path, lotID3
